﻿using System;
using System.Data.SqlServerCe;
using System.Linq;
using Dentiste.Domain.Entities;
using Dentiste.Domain.Factories;
using Dentiste.Domain.Interfaces;

namespace Dentiste.Domain.Providers
{
    internal class RowProvider : IProvider<IRow>
    {
        private readonly string _connectionString;
        private readonly InamiProvider _inamiProvider;

        #region Constructor

        public RowProvider(string connectionString, InamiProvider inamiProvider)
        {
            _connectionString = connectionString;
            _inamiProvider = inamiProvider;
        }

        #endregion

        public DbSetCustom<IRow> Select()
        {
            const string queryString = "SELECT * FROM DentisteRows";
            var ret = new DbSetCustom<IRow>(this);
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                while (reader != null && reader.Read())
                {
                    ret.Add(new Row
                        {
                            Id = reader.GetInt32(0),
                            Date = reader.GetDateTime(1),
                            Inami = _inamiProvider.Select(reader.GetString(2)),
                            ToothNumber = reader.GetInt32(3)
                        });
                }
            }
            return ret;
        }

        public IRow Select(string id)
        {
            const string queryString = "SELECT * FROM DentisteRows where Id=?";
            IRow r = null;
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Id", id);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.Read())
                {
                    r = new Row
                        {
                            Id = reader.GetInt32(0),
                            Date = reader.GetDateTime(1),
                            Inami = _inamiProvider.Select(reader.GetString(2)),
                            ToothNumber = reader.GetInt32(3)
                        };
                }
            }
            return r;
        }

        public IRow SelectLastInserted()
        {
            const string queryString = "SELECT max(id) FROM DentisteRows";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.Read())
                {
                    string id = Convert.ToString(reader.GetInt32(0));
                    return Select(id);
                }
            }
            return null;
        }

        public IRow Insert(IRow entity)
        {
            int ret;
            const string queryString = "INSERT INTO DentisteRows (InamiCode,DateRow,ToothNumber) VALUES (?, ?, ?)";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@InamiCode", entity.Inami.Code);
                cmd.Parameters.AddWithValue("@DateRow", entity.Date);
                cmd.Parameters.AddWithValue("@ToothNumber", entity.ToothNumber);

                ret = cmd.ExecuteNonQuery();
            }
            if (ret == 1)
            {
                return SelectLastInserted();
            }
            return null;
        }

        public bool Update(IRow entity)
        {
            int ret;
            const string queryString = "UPDATE DentisteRows SET InamiCode=?,DateRow=?,ToothNumber=? WHERE Id=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@InamiCode", entity.Inami.Code);
                cmd.Parameters.AddWithValue("@DateRow", entity.Date);
                cmd.Parameters.AddWithValue("@ToothNumber", entity.ToothNumber);
                cmd.Parameters.AddWithValue("@Id", entity.Id);

                ret = cmd.ExecuteNonQuery();
            }
            return ret == 1;
        }

        public bool Delete(IRow entity)
        {
            int ret;
            const string queryString = "DELETE FROM DentisteRows WHERE Id=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Id", entity.Id);

                ret = cmd.ExecuteNonQuery();
            }
            DeleteRowInvoiceAssociation(entity);
            return ret == 1;
        }

        private void DeleteRowInvoiceAssociation(IRow entity)
        {
            const string queryStringDelete = "DELETE FROM InvoiceRowRelations WHERE RowId=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryStringDelete, connection);
                cmd.Parameters.AddWithValue("@RowId", entity.Id);
                cmd.ExecuteNonQuery();
            }
        }
    }
}